﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DAL;

namespace KPIS.GERP.GBMS.DAL
{
    [Serializable]
    public sealed class StrategyDataMappingDAL : IStrategyDataMappingDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public int Insert(StrategyDataInfo strategyDataInfo)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@code", strategyDataInfo.Code);
                dbManager.AddParameters(1, "@name", strategyDataInfo.Name);
                dbManager.AddParameters(2, "@descr", strategyDataInfo.Description);
                dbManager.AddParameters(3, "@type", strategyDataInfo.StrategyType.Sequence);
                dbManager.AddParameters(4, "@status", SystemInfo._ActiveRecordStatus);

                return dbManager.ExecuteNonQuery(CommandType.Text, "INSERT INTO BGM_STRATEGY_DATA_LIST(BGM_STRAT_LIST_CODE, BGM_STRAT_LIST_NAME, BGM_STRAT_LIST_DESCR, BGM_STRAT_TYPE_SEQ, RECORD_STATUS) VALUES (@code, @name, @descr, @type, @status)");
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Update(StrategyDataInfo strategyDataInfo)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@seq", strategyDataInfo.Sequence);
                dbManager.AddParameters(1, "@code", strategyDataInfo.Code);
                dbManager.AddParameters(2, "@name", strategyDataInfo.Name);
                dbManager.AddParameters(3, "@descr", strategyDataInfo.Description);
                dbManager.AddParameters(4, "@type", strategyDataInfo.StrategyType.Sequence);
                dbManager.AddParameters(5, "@status", SystemInfo._ActiveRecordStatus);

                strSQL = "UPDATE BGM_STRATEGY_DATA_LIST"
                + " SET BGM_STRAT_LIST_CODE = @code"
                    + ", BGM_STRAT_LIST_NAME = @name"
                    + ", BGM_STRAT_LIST_DESCR = @descr"
                    + ", RECORD_STATUS = @status"
                + " WHERE BGM_STRAT_LIST_SEQ = @seq";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Delete(int seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                return dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE BGM_STRATEGY_DATA_LIST SET RECORD_STATUS = @status WHERE BGM_STRAT_LIST_SEQ = @seq");
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public IList<StrategyDataMappingInfo> Search(StrategyDataMappingInfo strategyDataInfo)
        {
            string strSQL;
            IList<StrategyDataMappingInfo> stratList = new List<StrategyDataMappingInfo>();
            int type = 0;
            int list = 0;
            int parent = 0;

            if (strategyDataInfo.DataList.StrategyType != null)
                type = strategyDataInfo.DataList.StrategyType.Sequence;

            if (strategyDataInfo.DataList != null)
                list = strategyDataInfo.DataList.Sequence;

            if (strategyDataInfo.Parent != null)
                parent = strategyDataInfo.Parent.Sequence;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@seq", strategyDataInfo.Sequence);
                dbManager.AddParameters(1, "@list", list);
                dbManager.AddParameters(2, "@parent", parent);
                dbManager.AddParameters(3, "@yearstart", strategyDataInfo.YearStart);
                dbManager.AddParameters(4, "@yearend", strategyDataInfo.YearEnd);
                dbManager.AddParameters(5, "@type", type);
                dbManager.AddParameters(6, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT mp.BGM_STRAT_DATA_MAP_SEQ, dl.BGM_STRAT_LIST_NAME, mp.BGM_STRAT_MAP_SEQ, mp.BGM_STRAT_LIST_SEQ"
                    + ", mp.UPPER_BGM_STRAT_DATA_MAP_SEQ"
                    + ", mp.YEAR_START, mp.YEAR_END, mp.RECORD_STATUS, mp.bgm_year"
                    + " FROM BGM_STRATEGY_DATA_MAPPING mp"
                        + " INNER JOIN BGM_STRATEGY_DATA_LIST dl ON mp.BGM_STRAT_LIST_SEQ = dl.BGM_STRAT_LIST_SEQ"
                    + " WHERE mp.RECORD_STATUS not like @status"
                        + ((strategyDataInfo.Sequence > 0) ? " AND mp.BGM_STRAT_DATA_MAP_SEQ = @seq" : "")
                        + ((list > 0) ? " AND mp.BGM_STRAT_LIST_SEQ = @list" : "")
                        + ((parent > 0) ? " AND mp.UPPER_BGM_STRAT_DATA_MAP_SEQ = @parent" : "")
                        + ((type > 0) ? " AND dl.BGM_STRAT_TYPE_SEQ = @type" : "")
                        + ((strategyDataInfo.YearStart > 0) ? " AND mp.BGM_YEAR = @yearstart" : "")
                        + ((strategyDataInfo.YearEnd > 0) ? " AND mp.BGM_YEAR = @yearend" : "");

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    StrategyDataListDAL stratCtrl = new StrategyDataListDAL();

                    IList<StrategyDataInfo> stratData = stratCtrl.Search(new StrategyDataInfo(rdr.GetInt32(3), "", "", "", "", "", "", "" , null, null, null));

                    StrategyDataMappingInfo ParentInfo = new StrategyDataMappingInfo();

                    if (rdr.IsDBNull(4))
                        ParentInfo = null;
                    else ParentInfo.Sequence = rdr.GetInt32(4);

                    StrategyDataMappingInfo stratRecord = new StrategyDataMappingInfo(rdr.GetInt32(0)
                        , rdr.GetString(1), null, stratData[0], ParentInfo
                        , (rdr.IsDBNull(5)) ? null : (Nullable<int>)rdr.GetInt32(5)
                        , (rdr.IsDBNull(6)) ? null : (Nullable<int>)rdr.GetInt32(6)
                        , rdr.GetString(7), null);
                    stratList.Add(stratRecord);
                }

                return stratList;
            }
            catch (Exception ex)
            {
                return null;
                throw ex;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
    }
}